using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public EnderecotipoVO getEnderecotipo(int? idEnderecoTipo, string enderecoTipo)
		{
			EnderecotipoVO enderecotipoVO = null;
			List<EnderecotipoVO> listEnderecotipoVOs = getEnderecotipos(idEnderecoTipo, enderecoTipo, null, null, null, null, null);
			if (listEnderecotipoVOs != null && listEnderecotipoVOs.Count > 0)
			{
				enderecotipoVO = listEnderecotipoVOs[0];
			}
			return enderecotipoVO;
		}

		public EnderecotipoVO getEnderecotipo(int? idEnderecoTipo)
		{
			return getEnderecotipo(idEnderecoTipo, null);
		}

		public List<EnderecotipoVO> getEnderecotipos()
		{
			return getEnderecotipos(null, null, null, null, null, null, null);
		}

		public List<EnderecotipoVO> getEnderecotipos(int? idEnderecoTipo)
		{
			return getEnderecotipos(idEnderecoTipo, null, null, null, null, null, null);
		}

		public List<EnderecotipoVO> getEnderecotipos(int? idEnderecoTipo, string enderecoTipo)
		{
			return getEnderecotipos(idEnderecoTipo, enderecoTipo, null, null, null, null, null);
		}

		public List<EnderecotipoVO> getEnderecotipos(int? idEnderecoTipo, string enderecoTipo, int? idFilial, bool? principal, bool? entrega, bool? cobrança, bool? corresp)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT ent.idEnderecoTipo as ent_idEnderecoTipo, ent.idFilial as ent_idFilial, fil.filial as fil_filial, ent.enderecoTipo as ent_enderecoTipo, ent.principal as ent_principal, ent.entrega as ent_entrega, ent.cobranca as ent_cobranca, ent.corresp as ent_corresp, ent.Cadastro as ent_Cadastro, ent.CadastradoPor as ent_CadastradoPor, ent.Edicao as ent_Edicao, ent.EditadoPor as ent_EditadoPor  ");
			query.AppendLine("FROM enderecotipo as ent LEFT OUTER JOIN filial as fil on ent.idFilial=fil.idFilial  ");
			if (idEnderecoTipo != null)
			{
				query.AppendLine(where + "ent.idEnderecoTipo=@idEnderecoTipo ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "ent.idFilial=@idFilial ");
				where = "AND ";
			}
			if (enderecoTipo != null)
			{
				query.AppendLine(where + "ent.enderecoTipo LIKE @enderecoTipo ");
				where = "AND ";
			}
			if (principal != null)
			{
				query.AppendLine(where + "ent.principal=@principal ");
				where = "AND ";
			}
			if (entrega != null)
			{
				query.AppendLine(where + "ent.entrega=@entrega ");
				where = "AND ";
			}
			if (cobrança != null)
			{
				query.AppendLine(where + "ent.cobranca=@cobranca ");
				where = "AND ";
			}
			if (corresp != null)
			{
				query.AppendLine(where + "ent.corresp=@corresp ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY ent.enderecoTipo ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idEnderecoTipo!= null) cmd.Parameters.Add(getParameter(cmd, "@idEnderecoTipo", idEnderecoTipo));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (enderecoTipo!= null) cmd.Parameters.Add(getParameter(cmd, "@enderecoTipo", "%"+enderecoTipo+"%"));
			if (principal!= null) cmd.Parameters.Add(getParameter(cmd, "@principal", principal.Value?"S":"N"));
			if (entrega!= null) cmd.Parameters.Add(getParameter(cmd, "@entrega", entrega.Value?"S":"N"));
			if (cobrança!= null) cmd.Parameters.Add(getParameter(cmd, "@cobranca", cobrança.Value?"S":"N"));
			if (corresp!= null) cmd.Parameters.Add(getParameter(cmd, "@corresp", corresp.Value?"S":"N"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<EnderecotipoVO> listEnderecotipo = new List<EnderecotipoVO>();

				while (reader.Read())
				{
					EnderecotipoVO enderecotipoVO = EnderecotipoVO.CreateVO();
					enderecotipoVO.idEnderecoTipo = reader.GetInt32(reader.GetOrdinal("ent_idEnderecoTipo"));
					enderecotipoVO.idFilial = reader.GetInt32(reader.GetOrdinal("ent_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						enderecotipoVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					enderecotipoVO.enderecoTipo = reader.GetString(reader.GetOrdinal("ent_enderecoTipo"));
					enderecotipoVO.principal = reader.GetString(reader.GetOrdinal("ent_principal")).Equals("S");
					enderecotipoVO.entrega = reader.GetString(reader.GetOrdinal("ent_entrega")).Equals("S");
					enderecotipoVO.cobranca = reader.GetString(reader.GetOrdinal("ent_cobranca")).Equals("S");
					enderecotipoVO.corresp = reader.GetString(reader.GetOrdinal("ent_corresp")).Equals("S");
					enderecotipoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("ent_Cadastro"));
					enderecotipoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("ent_CadastradoPor"));
					enderecotipoVO.edicao = reader.GetDateTime(reader.GetOrdinal("ent_Edicao"));
					enderecotipoVO.editadoPor = reader.GetString(reader.GetOrdinal("ent_EditadoPor"));


					listEnderecotipo.Add(enderecotipoVO);
				}
				reader.Close();
				return listEnderecotipo;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setEnderecotipo(EnderecotipoVO enderecotipoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE enderecotipo ");
			query.AppendLine("SET idFilial=@idFilial, enderecoTipo=@enderecoTipo, principal=@principal, entrega=@entrega, cobranca=@cobranca, corresp=@corresp,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idEnderecoTipo=@idEnderecoTipo ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idEnderecoTipo", enderecotipoVO.idEnderecoTipo));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", enderecotipoVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@enderecoTipo", enderecotipoVO.enderecoTipo));
			cmd.Parameters.Add(getParameter(cmd, "@principal", (enderecotipoVO.principal==null?" ":(enderecotipoVO.principal.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@entrega", (enderecotipoVO.entrega==null?" ":(enderecotipoVO.entrega.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cobranca", (enderecotipoVO.cobranca==null?" ":(enderecotipoVO.cobranca.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@corresp", (enderecotipoVO.corresp==null?" ":(enderecotipoVO.corresp.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", enderecotipoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", enderecotipoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", enderecotipoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", enderecotipoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newEnderecotipo(EnderecotipoVO enderecotipoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO enderecotipo ");
			query.AppendLine("  ( idFilial, enderecoTipo, principal, entrega, cobranca, corresp, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @idFilial, @enderecoTipo, @principal, @entrega, @cobranca, @corresp, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idFilial", enderecotipoVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@enderecoTipo", enderecotipoVO.enderecoTipo));
			cmd.Parameters.Add(getParameter(cmd, "@principal", (enderecotipoVO.principal==null?" ":(enderecotipoVO.principal.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@entrega", (enderecotipoVO.entrega==null?" ":(enderecotipoVO.entrega.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cobranca", (enderecotipoVO.cobranca==null?" ":(enderecotipoVO.cobranca.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@corresp", (enderecotipoVO.corresp==null?" ":(enderecotipoVO.corresp.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", enderecotipoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", enderecotipoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", enderecotipoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", enderecotipoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('enderecotipo') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delEnderecotipo(EnderecotipoVO enderecotipoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM enderecotipo ");
			query.AppendLine("WHERE idEnderecoTipo=@idEnderecoTipo ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idEnderecoTipo", enderecotipoVO.idEnderecoTipo));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountEnderecotipoxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM enderecotipo WHERE IdFilial=@IdFilial ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
